package part17JDBC数据库.员工管理.dao;
/**
 * 数据库操作dao层
 * @author Administrator
 *
 */

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import part17JDBC数据库.员工管理.pojo.Employee;

public class EmployeeDao {

	/**
	 * @param employee
	 *            添加的对象
	 * @return int 影响行数
	 */
	public int insert(Employee employee) {

		Connection conn = null;
		PreparedStatement pstat = null;
		int n = 0;
		try {
			// ①导入数据库驱动jar包,加载驱动类
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jja1912_1", "root", "root");
			pstat = conn.prepareStatement("insert into employee values(null,?,?)");
			// 对占位符赋值
			pstat.setString(1, employee.getEmpName());
			pstat.setFloat(2, employee.getSalary());

			// 执行SQL
			n = pstat.executeUpdate();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				// ⑤关闭数据库：先创建的对象后关闭。
				if (pstat != null) {
					pstat.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return n;

	}

	/**
	 * 根据员工编号进行删除
	 * 
	 * @param empNo
	 *            员工编号
	 * @return 返回影响行数
	 */
	public int deleteById(Integer empNo) {
		Connection conn = null;
		PreparedStatement pstat = null;
		int n = 0;
		try {
			// ①导入数据库驱动jar包,加载驱动类
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jja1912_1", "root", "root");
			pstat = conn.prepareStatement("delete from employee where emp_no = ?");
			// 对占位符赋值
			pstat.setInt(1, empNo);
			// 执行SQL
			n = pstat.executeUpdate();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				// ⑤关闭数据库：先创建的对象后关闭。
				if (pstat != null) {
					pstat.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return n;
	}

	/**
	 * 查询所有员工列表
	 */
	public ArrayList<Employee> query() {
		// 定义一个集合存储所有的列表
		ArrayList<Employee> list = new ArrayList<>();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet resultSet = null;
		try {
			// ①导入数据库驱动jar包,加载驱动类
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jja1912_1", "root", "root");
			pstat = conn.prepareStatement("select * from employee");
			// 执行SQL
			resultSet = pstat.executeQuery();
			// 遍历结果集
			while (resultSet.next()) {
				int empNo = resultSet.getInt("emp_no");
				String empName = resultSet.getString("emp_name");
				float salary = resultSet.getFloat("salary");
				// 往集合中添加一个对象
				list.add(new Employee(empNo, empName, salary));
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				// ⑤关闭数据库：先创建的对象后关闭。
				if (resultSet != null) {
					resultSet.close();
				}
				if (pstat != null) {
					pstat.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	/**
	 * 根据员工编号查询单个
	 * 
	 * @param empNo
	 *            员工编号
	 * @return 员工对象
	 */
	public Employee queryById(Integer empNo) {
		// 定义一个对象
		Employee employee = null;
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet resultSet = null;
		try {
			// ①导入数据库驱动jar包,加载驱动类
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jja1912_1", "root", "root");
			pstat = conn.prepareStatement("select * from employee where emp_no = ?");
			pstat.setInt(1, empNo);
			// 执行SQL
			resultSet = pstat.executeQuery();
			// 遍历结果集
			while (resultSet.next()) {
//				int empNo = resultSet.getInt("emp_no");
				String empName = resultSet.getString("emp_name");
				float salary = resultSet.getFloat("salary");
				// 实例化对象
				employee = new Employee(empNo, empName, salary);
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				// ⑤关闭数据库：先创建的对象后关闭。
				if (resultSet != null) {
					resultSet.close();
				}
				if (pstat != null) {
					pstat.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return employee;
	}

	/**
	 * 根据用户名模糊查询
	 * 
	 * @param empName
	 *            员工姓名的关键字
	 * @return 员工列表
	 */
	public ArrayList<Employee> queryByEmployeeName(String empNameKey) {

		return null;
	}

	/**
	 * 根据员工编号修改员工信息
	 * 
	 * @param employee
	 *            修改的对象
	 * @return int 影响行数
	 */
	public int updateById(Employee employee) {

		return 0;
	}

	/**
	 * 根据姓氏进行模糊查询
	 * 
	 * @param empName
	 *            员工姓氏的关键字
	 * @return 员工列表 以empNameKey开头的姓名，也就是例如 传入陈，查姓氏为"陈"的所有员工????
	 */
	public ArrayList<Employee> queryByEmployeeName2(String empNameKey) {

		return null;
	}

}
